package com.cqeec.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import com.cqeec.bean.LendingInfo;
import com.cqeec.util.DBUtil;

public class LendingAction {
	Connection con = null;

	public LendingAction() {
		con = DBUtil.getConnection();
	}

	/**
	 * 
	 * @param 借阅信息对象
	 * @return 存放借阅信息的二维数组
	 */
	public String[][] getLendingList(LendingInfo info) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "select * from v_lendinginfo where 1=1 ";
		if (!info.getBookid().trim().equals("")) {
			sql = sql + " and bookid='" + info.getBookid().trim() + "'";
		}
		// {"图书编号", "图书名称", "图书类别","读者编号", "读者姓名", "联系电话", "借阅日期", "归还日期","经办人"
		// };
		if (!info.getBookname().trim().equals("")) {
			sql = sql + " and bookname like '%" + info.getBookname().trim()
					+ "%'";
		}
		if (!info.getBooktype().trim().equals("")) {
			sql = sql + " and booktype='" + info.getBooktype().trim() + "'";
		}
		if (!info.getReaderid().trim().equals("")) {
			sql = sql + " and readerid='" + info.getReaderid().trim() + "'";
		}
		if (!info.getReadername().trim().equals("")) {
			sql = sql + " and readername like '%" + info.getReadername().trim()
					+ "%'";
		}
		if (!info.getTel().trim().equals("")) {
			sql = sql + " and tel='" + info.getTel().trim() + "'";
		}
		if (!info.getStartlendDate().trim().equals("")) {
			sql = sql + " and lendingdate>='" + info.getStartlendDate().trim()
					+ "'";
		}
		if (!info.getEndlendDate().trim().equals("")) {
			sql = sql + " and lendingdate<='" + info.getEndlendDate().trim()
					+ "'";
		}
		if (!info.getStartbackDate().trim().equals("")) {
			sql = sql + " and returndate>='" + info.getStartbackDate().trim()
					+ "'";
		}
		if (!info.getEndbackDate().trim().equals("")) {
			sql = sql + " and returndate<='" + info.getEndbackDate().trim()
					+ "'";
		}
		if (!info.getRealname().trim().equals("")) {
			sql = sql + " and realname like '%" + info.getRealname().trim()
					+ "%'";
		}
		ResultSet rs = null;
		String sn[][] = null;
		int row = 0;
		try {
			Statement stat = con.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stat.executeQuery(sql);
			if (rs.last()) {
				row = rs.getRow();
			}
			if (row != 0) {
				sn = new String[row][9];
				rs.first();
				rs.previous();
				int i = 0;
				while (rs.next()) {
					sn[i][0] = rs.getString("bookid");
					sn[i][1] = rs.getString("bookname");
					sn[i][2] = rs.getString("booktype");
					sn[i][3] = rs.getString("readerid");
					sn[i][4] = rs.getString("readername");
					sn[i][5] = rs.getString("tel");
					sn[i][6] = sdf.format(rs.getDate("lendingdate"));
					if (rs.getDate("returndate") != null) {
						sn[i][7] = sdf.format(rs.getDate("returndate"));
					} else {
						sn[i][7] = rs.getString("returndate");
					}
					sn[i][8] = rs.getString("realname");
					i++;
				}
				rs.close();
				stat.close();
			}
		} catch (Exception e) {
		}
		return sn;
	}
	/**
	 * @description 借书或还书前先检查当前读者是否已经借过此书
	 * @param readerid 读者编号
	 * @param bookid  图书编号
	 * @return
	 */
	public boolean beforeBorrowOrReturn(String readerid,String bookid){
		String sql = "select count(*) from lendinginfo where readerid=? and bookid=? and returndate is null";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			pstm.setString(2, bookid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				int count = rs.getInt(1);
				if (count == 0){
					return true; //未借或已还
				}else{
					return false;//已借
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 借书完成后图书对应的数量-1
	 * @param readerid 读者编号
	 * @param bookid  图书编号
	 * @param userid  操作人
	 * @return
	 */
	public boolean borrowBook(String readerid,String bookid,int userid){
		String sql = "Insert into lendinginfo(readerid,bookid,lendingdate,userid) values(?,?,getdate(),?)";
		try{
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			pstm.setString(2, bookid);
			pstm.setInt(3, userid);
			pstm.executeUpdate();
			return true;
		}catch(SQLException e){
			return false;
		}
	}
	/**
	 * 还书成功后图书对应的数量+1
	 * @param readerid 读者编号
	 * @param bookid  图书编号
	 * @return
	 */
	public boolean returnBook(String readerid,String bookid){
		String sql = "update lendinginfo set returndate=getdate() where readerid=? and bookid=?";
		try{
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			pstm.setString(2, bookid);
			pstm.executeUpdate();
			return true;
		}catch(SQLException e){
			return false;
		}
	}
	/**
	 * 检查所借图书是否还有
	 * @param bookid 图书编号
	 * @return 图书数量不为0返回true，否则返回false
	 */
	public boolean beforeBorrowCheckStockNum(String bookid){
		String sql = "select stocknum from BookInfo where bookid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, bookid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				if (rs.getInt("stocknum") > 0){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 按当前年份分月份统计借阅次数
	 * @return
	 */
	public String[][] HsyLendTJ(){
		String[][] sn = null;
		int row = 0;
		int i=0;
		String sql = "select MONTH(lendingdate) months,COUNT(*) c from LendingInfo";
		sql = sql + " where YEAR(lendingdate) = YEAR(getdate()) group by MONTH(lendingdate)";
		try {
			Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stm.executeQuery(sql);
			if (rs.last()){
				row = rs.getRow();
				if (row != 0){
					sn = new String[row][2];
					rs.first();
					rs.previous();
					while(rs.next()){
						sn[i][0] = rs.getInt("c") + "";
						sn[i][1] = rs.getString("months") + "月";
						i++;
					}
					rs.close();
					stm.close();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sn;
	}
	
	/**
	 * 图书排行统计
	 * @return
	 */
	public String[][] RanklistTJ(){
		String[][] sn = null;
		int row = 0;
		int i=0;		
		String sql = "select top 10 bookname,c from BookInfo join(";
		sql = sql + " select bookid,COUNT(*) c from LendingInfo group by bookid) ";
		sql = sql + " as b on bookinfo.bookid = b.bookid";
		
		try {
			Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stm.executeQuery(sql);
			if (rs.last()){
				row = rs.getRow();
				if (row != 0){
					sn = new String[row][2];
					rs.first();
					rs.previous();
					while(rs.next()){
						sn[i][0] = rs.getInt("c") + "";
						sn[i][1] = rs.getString("bookname");
						i++;
					}
					rs.close();
					stm.close();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sn;
	}
}
